package jk.spider.util.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import jk.spider.util.SpiderUtil;
import jk.spider.util.config.ConfigConstants;
import jk.spider.util.config.MappedPropertySet;
import jk.spider.util.config.PropertySet;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.log4j.Logger;

public class DBHelp {
	protected static final Logger log = Logger.getLogger(DBHelp.class);
	public static final String JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	public static final String USER = "sa";
	public static final String PASSWORD = "mssql!@#$";
	//public static final String URL = "jdbc:mysql://192.168.64.168:3306/dkuai_development?characterEncoding=utf-8";
	public static final String URL = "jdbc:sqlserver://192.168.2.178:1433;DatabaseName=spider";

	protected static Connection connection;
	private static DBHelp dbHelp;
	private static PropertySet props;
	
	private DBHelp(PropertySet props) {
		PropertySet dbProps = new MappedPropertySet ( ConfigConstants.CONFIG_DB, props );
		DbUtils.loadDriver(dbProps.getString(ConfigConstants.DB_DRIVER, JDBC_DRIVER));
		try {
			String url = dbProps.getString(ConfigConstants.DB_URL, URL);
			String user = dbProps.getString(ConfigConstants.DB_USER, USER);
			String password = dbProps.getString(ConfigConstants.DB_PASSWORD, PASSWORD);
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private DBHelp(String db_driver, String db_user, String pwd, String db_url) {
		DbUtils.loadDriver(db_driver);
		try {
			String url = db_url;
			String user = db_user;
			String password = pwd;
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static final void initialization(PropertySet prs) {
		 props = prs;
	}
	
	public static final synchronized DBHelp getInstant() {
		if(connection == null)
			dbHelp = new DBHelp(props);
		return dbHelp;
	}
	
	public static final synchronized DBHelp getInstant(String driver, String user, String pwd, String url) {
		if(connection == null)
			dbHelp = new DBHelp(driver, user, pwd, url);
		return dbHelp;
	}
	
	public DBHelp() {
		DbUtils.loadDriver(JDBC_DRIVER);
		try {
			connection = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}  
	}
	
	public List<Map<String, Object>> query(String sql) {
		QueryRunner qr = new QueryRunner();  
		try {
			return (List<Map<String, Object>>) qr.query(connection, sql, new MapListHandler());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Object[] select(String sql) {
		ResultSetHandler h = new ResultObjectsHandler();
		QueryRunner qr = new QueryRunner();  
		try {
			return (Object[]) qr.query(connection, sql, h);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public int execInorUpd(String sql) {
		QueryRunner qr = new QueryRunner();  
		try {
			return qr.update(connection, sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public int execInorUpd(String sql, String... params) {
		QueryRunner qr = new QueryRunner();  
		try {
			return qr.update(connection, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public void updateUrlError(String url) {
		String sql = "update competitor_products set flag = 3, updated_at = '"+ new Date().toLocaleString() +"' where url = '"+ url +"'" ;
		this.execInorUpd(sql);
	}
	
	public String format(int i) {
		return "" + i;
	}
	
	public String format(String str) {
		return "'" + this.formatSQL(str) + "'" ;
	}
	
	public String formatSQL (String str) {
		if(!SpiderUtil.isStringNull(str))
			return str.replaceAll("'", "''");
		else
			return str;
	}
	
	public static void main(String[] args) {
		DBHelp dbHelp = new DBHelp();
		System.out.println(dbHelp.select("select count(*) from mandao.users")[0]);
		Object[] obj = dbHelp.select("select * from mandao.users where username = 'ppppp' and rownum <= 1" );
		System.out.println(obj.length);
		
	}
}


